package com.bw.mapper;

import com.bw.domain.Sugar;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Controller;

import java.util.List;

@Mapper
public interface SugarMapper {
    @Select("SELECT p.`name` name,sum(o.final_total_amount) value FROM order_info o,base_province p WHERE o.province_id=p.id GROUP BY p.`name`")
    List<Sugar> getSugarList1();

    @Select("select  sum(o.final_total_amount) 'value',\n" +
            "concat(SUBSTRING(year(birthday),3,1),\"0后\") 'name' \n" +
            "from order_info  o\n" +
            "LEFT JOIN user_info u  ON  o.user_id=u.id\n" +
            "group By concat(SUBSTRING(year(birthday),3,1),\"0后\") \n" +
            "order  BY value")
    List<Sugar> getSugarList2();

    @Select("select yys `name`,sum(final_total_amount) `value` from order_info  where yys is not NULL GROUP BY yys")
    List<Sugar> getSugarList3();

    @Select("select t.name,\n" +
            " if(u.gender='F','男','女') gender,\n" +
            " sum(o.final_total_amount) `value` \n" +
            "from order_info o\n" +
            " LEFT JOIN user_info u ON o.user_id=u.id\n" +
            " LEFT JOIN order_detail d ON d.order_id=o.id\n" +
            " LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            " LEFT JOIN base_trademark t ON s.tm_id=t.id\n" +
            " GROUP BY t.name,u.gender")
    List<Sugar> getSugarList4();
}
